package cn.huihuo.weibo.module;


import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;

import java.util.List;

/**
 * Created by Administrator on 2015/8/25.
 */
public class ProdComment extends Model<ProdComment> {
    public static final ProdComment prodComment = new ProdComment();

    /**
     * 微博要关联商品
     * @param id 商品评论ID
     * @return
     */
    public ProdComment findWeiBoById(String id){
        return findFirst("select * from prodComment t,product p where t.prod_id=p.id and t.id= ?",id);
    }

    public ProdComment findWeiBoByProdId(String prodId){
        return findFirst("select * from prodComment t where t.prod_id = ?",prodId);
    }


    /**
     * 统计商品页下的数量
     * @param prodId
     * @param ugcType
     * @return
     */
    public ProdComment prodTypeTotalSize(String prodId,String ugcType){
        return findFirst("select count(1) size from prodcomment t where t.prod_id=? and t.ugc_type=?",prodId,ugcType);
    }


    /**
     * 统计ugc
     * @param prodId
     * @return
     */
    public ProdComment prodUgcSize(Integer prodId){
        return findFirst("select count(1) size from prodcomment t where t.prod_id=?",prodId);
    }

    /**
     * 统计评论的数量
     * @param prodId
     * @return
     */
    public ProdComment prodCommentSize(Integer prodId){
        return findFirst("select count(1) size from prodcomment t right join comment c on t.id=c.ugc_id where t.prod_id=?",prodId);
    }

    /**
     * 统计投票的数量
     * @param prodId
     * @return
     */
    public ProdComment prodChannelBillSize(Integer prodId){
        return findFirst("SELECT (IFNULL(SUM(t.good_channel),0)+ IFNULL(SUM(t.normal_channel),0)+ IFNULL(SUM(t.bad_channel),0)) size FROM prodcomment t WHERE t.prod_id=?",prodId);
    }





    public Page<ProdComment> findProductCommentBuyProdId(int pageNumber, int pageSize, String prodId){
        return super.paginate(pageNumber, pageSize, "select * ", "from prodComment where prod_id = ? order by product_comment_time desc", prodId);
    }

    public Page<ProdComment> findAllProductCommentByProdId(int pageNumber,int pageSize,String prodid){
        return super.paginate(pageNumber, pageSize, "select * ", "from prodComment where prod_id = ? order by product_comment_time desc",prodid);
    }

    public Page<ProdComment> findAllProductComment(int pageNumber,int pageSize){
        return super.paginate(pageNumber, pageSize, "select * ", "from prodComment order by product_comment_time desc");
    }

    public List<ProdComment> findProductComment(String prodId){
        return find("select * from prodcomment t where t.prod_id=?",prodId);
    }


    public Page<ProdComment> findUGCByType(int pageNumber,int pageSize,String productId,String ugcType){
        return paginate(pageNumber,pageSize,"SELECT pc.id ugcId, pc.product_comment comm, u.id userId, pc.ugc_type ugcType, pc.url url, pc.product_comment_time commTime, pc.good_channel goodChannel, pc.normal_channel normalChannel, pc.bad_channel badChannel,u.nickname nickname,u.avatar avatar"
        ," FROM prodcomment pc, USER u,( SELECT p.id prodId,pic.out_pic_path,pic.inner_pic_path,p.prod_title,p.price FROM product p,productpic pic WHERE p.defult_pic_id = pic.id) pp WHERE pc.prod_id=pp.prodId AND pc.user_id=u.id  AND PP.prodId=? AND pc.ugc_type=? order by product_comment_time desc"
        ,productId,ugcType);
    }

    public Page<ProdComment> findUGCPageByUgcId(String ugcId){
        return paginate(1,1,"SELECT pc.id ugcId, pc.product_comment comm, u.id userId, pc.ugc_type ugcType, pc.url url, pc.product_comment_time commTime, pc.good_channel goodChannel, pc.normal_channel normalChannel, pc.bad_channel badChannel,u.nickname nickname,u.avatar avatar"
                ," FROM prodcomment pc, USER u,( SELECT p.id prodId,pic.out_pic_path,pic.inner_pic_path,p.prod_title,p.price FROM product p,productpic pic WHERE p.defult_pic_id = pic.id) pp WHERE pc.prod_id=pp.prodId AND pc.user_id=u.id  AND pc.id=? "
                ,ugcId);
    }

    public ProdComment findUGCByUgcId(String ugcId){
        return findFirst("SELECT t.id prodId,t.prod_title ,t.price ,comm.id ugcId,comm.product_comment_time commTime,pic.out_pic_path,pic.inner_pic_path FROM product t,prodcomment comm,productpic pic WHERE t.id=comm.prod_id AND pic.id=t.defult_pic_id AND comm.id=?",ugcId);
    }

    public Integer updateGoodChannel(Integer ugcId,Integer goodChannel,Integer normalChannel,Integer badChannel){
        return Db.update("update prodcomment t set t.good_channel=t.good_channel+?,t.normal_channel=t.normal_channel+?,t.bad_channel=t.bad_channel+? where t.id=?",goodChannel,normalChannel,badChannel,ugcId);
    }


    public Page<ProdComment> findAllDynamic(Integer pageNumber,Integer pageSize){
        return paginate(pageNumber,pageSize,"SELECT pc.id ugcId, pc.product_comment comm, u.id userId, pc.ugc_type ugcType, pc.url url,pc.product_comment_time commTime,u.nickname nickname,u.avatar avatar,pp.prodId,pp.out_pic_path,pp.inner_pic_path ,pp.title"," FROM prodcomment pc, USER u,(SELECT p.id prodId,pic.out_pic_path,pic.inner_pic_path,p.prod_title,p.price ,p.prod_title title FROM product p,productpic pic WHERE p.defult_pic_id = pic.id) pp WHERE pc.prod_id=pp.prodId AND pc.user_id=u.id ORDER BY product_comment_time DESC");
    }

    public Page<ProdComment> findAllDynamic(Integer pageNumber,Integer pageSize,String userId){
        return paginate(pageNumber,pageSize,"SELECT pc.id ugcId, pc.product_comment comm, u.id userId, pc.ugc_type ugcType, pc.url url,pc.product_comment_time commTime,u.nickname nickname,u.avatar avatar,pp.prodId,pp.out_pic_path,pp.inner_pic_path ,pp.title"," FROM prodcomment pc, USER u,(SELECT p.id prodId,pic.out_pic_path,pic.inner_pic_path,p.prod_title,p.price ,p.prod_title title FROM product p,productpic pic WHERE p.defult_pic_id = pic.id) pp WHERE pc.prod_id=pp.prodId AND pc.user_id=u.id and u.id=? ORDER BY product_comment_time DESC",userId);
    }



    public ProdComment findAllComment(String prodCommentId){
        return findFirst("SELECT pic.out_pic_path outPicPath," +
                "pic.inner_pic_path innerPicPath," +
                "pic.id picId," +
                "t.product_comment_time prodCommentTime," +
                "t.product_comment prodComment," +
                "t.praise praise," +
                "t.id prdCommentId," +
                "p.prod_title prodTitle," +
                "p.price price," +
                "p.price_current priceCurrent," +
                "p.id prodId," +
                "u.id userId," +
                "u.nickname nickname," +
                "u.avatar avatar " +
                "FROM prodcomment t,product p, USER u, productpic pic " +
                "WHERE t.prod_id=p.id AND t.user_id=u.id AND pic.id= t.pic_id " +
                " and t.id=?",prodCommentId);
    }

    public List<ProdComment> findHotProd(Integer prodId,String ugcType){
        return find("SELECT pt.id ugcId,pt.product_comment,pt.ugc_type,pt.url,u.id userId,u.nickname FROM ugc_hot h,prodcomment pt, USER u WHERE h.ugc_id=pt.id AND u.id=pt.user_id AND pt.prod_id = ? AND h.ugc_type= ? ORDER BY h.hot_weight DESC",prodId,ugcType);
    }

    public List<ProdComment> findByProdId(Integer prodId){
        return find("select * from prodcomment t where t.prod_id=?",prodId);
    }

    public ProdComment findByProdIdNew(Integer prodId){
        return findFirst("SELECT t.ugc_type ,t.product_comment,t.product_comment_time comm_time,u.nickname,u.avatar,u.id userId,t.url FROM prodcomment t, USER u WHERE t.user_id=u.id AND t.prod_id=? AND t.ugc_type != 1 ORDER BY product_comment_time DESC",prodId);
    }

    public ProdComment findSameUrl(Integer prodId,String ugcType,String url){
        return findFirst("select * from prodcomment t where t.prod_id=? and t.ugc_type=? and t.url=?",prodId,ugcType,url);
    }

}
